using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections;
using System.Xml;

namespace ETUDataControls
{
    public sealed class ETUSQLDataLayer : SQLDataLayerControl
    {
        private SqlParameter[] commandParameters;
        private Hashtable paramCache = Hashtable.Synchronized(new Hashtable());
        private string connectString;
       
        public override System.Data.DataTable GetTable(System.Data.SqlClient.SqlDataReader _reader)
        {
            System.Data.DataTable _table = _reader.GetSchemaTable();
            System.Data.DataTable _dt = new System.Data.DataTable();
            System.Data.DataColumn _dc;
            System.Data.DataRow _row;
            System.Collections.ArrayList _al = new System.Collections.ArrayList();

            for (int i = 0; i < _table.Rows.Count; i++)
            {
                _dc = new System.Data.DataColumn();

                if (!_dt.Columns.Contains(_table.Rows[i]["ColumnName"].ToString()))
                {
                    _dc.ColumnName = _table.Rows[i]["ColumnName"].ToString();
                    _dc.Unique = Convert.ToBoolean(_table.Rows[i]["IsUnique"]);
                    _dc.AllowDBNull = Convert.ToBoolean(_table.Rows[i]["AllowDBNull"]);
                    _dc.ReadOnly = Convert.ToBoolean(_table.Rows[i]["IsReadOnly"]);
                    _al.Add(_dc.ColumnName);
                    _dt.Columns.Add(_dc);
                }
            }

            while (_reader.Read())
            {
                _row = _dt.NewRow();

                for (int i = 0; i < _al.Count; i++)
                {
                    _row[((System.String)_al[i])] = _reader[(System.String)_al[i]];
                }
                _dt.Rows.Add(_row);
            }
            return _dt;
        }
        public override void ServeSQLStatements(string rawSQLQuery)
        {
            SqlConnection cn;
            if (string.IsNullOrEmpty(connectString))
            {
                Connection cn1 = new Connection();
                cn = cn1.SqlConnect;
                cn.ConnectionString = cn1.ConnectString;
            }
            else
            {

                cn = new SqlConnection(connectString);
            }
            DataSet ds = new DataSet();
            try
            {
                cn.Open();

                SqlCommand cmd = new SqlCommand();
                cmd.CommandText = rawSQLQuery;
                cmd.CommandTimeout = 40000;
                cmd.CommandType = CommandType.Text;
                cmd.Connection = cn;

                cmd.ExecuteNonQuery();


            }
            catch (Exception ex)
            {
                //ExceptionLogManager.LogException(ex, "Generated from file ETUDataControls -> ETUSQLDataLayer.cs; Method ExecuteDataSet", "ETUDataControls", null);
                throw new Exception(ex.Message, ex.InnerException);
            }
            finally
            {
                if (cn.State == ConnectionState.Open)
                    cn.Close();
            }
            
        }
       
        public override DataTable GetTable(string rawSQLQuery)
        {
            SqlConnection cn;
            if (string.IsNullOrEmpty(connectString))
            {
                Connection cn1 = new Connection();
                cn = cn1.SqlConnect;
                cn.ConnectionString = cn1.ConnectString;
            }
            else
            {

                cn = new SqlConnection(connectString);
            }
            DataSet ds = new DataSet();
            try
            {
                cn.Open();

                
                SqlCommand cmd = new SqlCommand();
                cmd.CommandText = rawSQLQuery;
                cmd.CommandTimeout = 40000;
                cmd.CommandType = CommandType.Text;
                cmd.Connection = cn;

                SqlDataAdapter da = new SqlDataAdapter(cmd);

                da.Fill(ds);
                

            }
            catch (Exception ex)
            {
                //ExceptionLogManager.LogException(ex, "Generated from file ETUDataControls -> ETUSQLDataLayer.cs; Method ExecuteDataSet", "ETUDataControls", null);
                throw new Exception(ex.Message, ex.InnerException);
            }
            finally
            {
                if (cn.State == ConnectionState.Open)
                    cn.Close();
            }
            return ds.Tables[0];
        }
        public override DataTable GetTable(string connectString, string rawSQLQuery)
        {
            SqlConnection cn;
            if (string.IsNullOrEmpty(connectString))
            {
                Connection cn1 = new Connection();
                cn = cn1.SqlConnect;
                cn.ConnectionString = cn1.ConnectString;
            }
            else
            {

                cn = new SqlConnection(connectString);
            }
            DataSet ds = new DataSet();
            try
            {
                cn.Open();

                SqlCommand cmd = new SqlCommand();
                cmd.CommandText = rawSQLQuery;
                cmd.CommandTimeout = 40000;
                cmd.CommandType = CommandType.Text;
                cmd.Connection = cn;

                //create the DataAdapter & DataSet
                SqlDataAdapter da = new SqlDataAdapter(cmd);

                //fill the DataSet using default values for DataTable names, etc.
                da.Fill(ds);

                // detach the SqlParameters from the command object, so they can be used again.			
                cmd.Parameters.Clear();


            }
            catch (Exception ex)
            {
                //ExceptionLogManager.LogException(ex, "Generated from file ETUDataControls -> ETUSQLDataLayer.cs; Method ExecuteDataSet", "ETUDataControls", null);
                throw new Exception(ex.Message, ex.InnerException);
            }
            finally
            {
                if (cn.State == ConnectionState.Open)
                    cn.Close();
            }
            return ds.Tables[0];
        }
        public override SqlDataReader ExecuteReader(string connectNewString, string spName, params object[] parameterValues)
        {
            Connection cn1 = new Connection();
            SqlConnection cn = cn1.SqlConnect;
            cn.ConnectionString = connectNewString;
            //create a reader
            SqlDataReader dr = null;
            try
            {
                cn.Open();
                //ConnectionMonitor cm = new ConnectionMonitor();
                //cm.Add(new ConnectionInfo(cn));
                if ((parameterValues != null) && (parameterValues.Length > 0))
                {
                    //pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
                    commandParameters = GetSpParameterSet(cn, spName, false);

                    //assign the provided values to these parameters based on parameter order
                    AssignParameterValues(commandParameters, parameterValues);
                }

                SqlCommand cmd = new SqlCommand();
                PrepareCommand(cmd, cn, (SqlTransaction)null, CommandType.StoredProcedure, spName, commandParameters);

                dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

                // detach the SqlParameters from the command object, so they can be used again.
                cmd.Parameters.Clear();

            }
            catch (Exception ex)
            {
                //ExceptionLogManager.LogException(ex, "Generated from file ETUDataControls -> ETUSQLDataLayer.cs; Method ExecuteReader", "ETUDataControls", null);
                throw new Exception(ex.Message, ex.InnerException);
            }
            finally
            {
                if (cn.State == ConnectionState.Open)
                    cn.Close();
            }
            return dr;

        }
        //new method
        public override SqlDataReader ExecuteReader(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
        {
            SqlConnection cn;
            if (string.IsNullOrEmpty(connectString))
            {
                Connection cn1 = new Connection();
                cn = cn1.SqlConnect;
                cn.ConnectionString = cn1.ConnectString;
            }
            else
            {

                cn = new SqlConnection(connectString);
            }
            //create a reader
            SqlDataReader dr = null;
            try
            {
                cn.Open();
                SqlCommand cmd = new SqlCommand();
                PrepareCommand(cmd, cn, (SqlTransaction)null, commandType, commandText, commandParameters);

                //create a reader
                dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

                // detach the SqlParameters from the command object, so they can be used again.
                cmd.Parameters.Clear();

            }
            catch (Exception ex)
            {
                //ExceptionLogManager.LogException(ex, "Generated from file ETUDataControls -> ETUSQLDataLayer.cs; Method ExecuteReader", "ETUDataControls", null);
                throw new Exception(ex.Message, ex.InnerException);
            }
            finally
            {
                if (cn.State == ConnectionState.Open)
                    cn.Close();
            }
            return dr;

        }

        public override SqlDataReader ExecuteReader(string spName, params object[] parameterValues)
        {
            SqlConnection cn;
            if (string.IsNullOrEmpty(connectString))
            {
                Connection cn1 = new Connection();
                cn = cn1.SqlConnect;
                cn.ConnectionString = cn1.ConnectString;
            }
            else
            {

                cn = new SqlConnection(connectString);
            }
            //create a reader
            SqlDataReader dr = null;
            try
            {
                cn.Open();
                //ConnectionMonitor cm = new ConnectionMonitor();
                //cm.Add(new ConnectionInfo(cn));
                if ((parameterValues != null) && (parameterValues.Length > 0))
                {
                    //pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
                    commandParameters = GetSpParameterSet(cn, spName, false);

                    //assign the provided values to these parameters based on parameter order
                    AssignParameterValues(commandParameters, parameterValues);
                }

                SqlCommand cmd = new SqlCommand();
                PrepareCommand(cmd, cn, (SqlTransaction)null, CommandType.StoredProcedure, spName, commandParameters);

                dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

                // detach the SqlParameters from the command object, so they can be used again.
                cmd.Parameters.Clear();

            }
            catch (Exception ex)
            {
                //ExceptionLogManager.LogException(ex, "Generated from file ETUDataControls -> ETUSQLDataLayer.cs; Method ExecuteReader", "ETUDataControls", null);
                throw new Exception(ex.Message, ex.InnerException);
            }
            finally
            {
                if (cn.State == ConnectionState.Open)
                    cn.Close();
            }
            return dr;

        }


        private SqlParameter[] DiscoverSpParameterSet(SqlConnection cn, string spName, bool includeReturnValueParameter)
        {
            using (SqlCommand cmd = new SqlCommand(spName, cn))
            {
                cmd.CommandType = CommandType.StoredProcedure;

                SqlCommandBuilder.DeriveParameters(cmd);

                if (!includeReturnValueParameter)
                {
                    cmd.Parameters.RemoveAt(0);
                }

                SqlParameter[] discoveredParameters = new SqlParameter[cmd.Parameters.Count];

                cmd.Parameters.CopyTo(discoveredParameters, 0);
                cmd.Parameters.Clear();
                return discoveredParameters;
            }
        }
        private void CacheParameterSet(string connectionString, string commandText, params SqlParameter[] commandParameters)
        {
            if (paramCache != null)
            {
                string hashKey = connectionString + ":" + commandText;
                paramCache[hashKey] = commandParameters;
            }
        }

        /// <summary>
        /// retrieve a parameter array from the cache
        /// </summary>
        /// <param name="connectionString">a valid connection string for a SqlConnection</param>
        /// <param name="commandText">the stored procedure name or T-SQL command</param>
        /// <returns>an array of SqlParamters</returns>
        private SqlParameter[] GetCachedParameterSet(string connectionString, string commandText)
        {
            if (paramCache == null)
            {
                return null;
            }

            string hashKey = connectionString + ":" + commandText;

            SqlParameter[] cachedParameters = (SqlParameter[])paramCache[hashKey];

            if (cachedParameters == null)
            {
                return null;
            }
            else
            {
                return CloneParameters(cachedParameters);
            }
        }
        private SqlParameter[] GetSpParameterSet(SqlConnection connection, string spName, bool includeReturnValueParameter)
        {
            string hashKey = spName + (includeReturnValueParameter ? ":include ReturnValue Parameter" : "");

            SqlParameter[] cachedParameters = null;

            if (paramCache != null)
            {
                cachedParameters = (SqlParameter[])paramCache[hashKey];
            }

            if (cachedParameters == null)
            {
                cachedParameters = DiscoverSpParameterSet(connection, spName, includeReturnValueParameter);
                if (paramCache != null)
                {
                    paramCache[hashKey] = cachedParameters;
                }
            }

            return CloneParameters(cachedParameters);
        }
        //deep copy of cached SqlParameter array
        private SqlParameter[] CloneParameters(SqlParameter[] originalParameters)
        {
            SqlParameter[] clonedParameters = new SqlParameter[originalParameters.Length];

            for (int i = 0, j = originalParameters.Length; i < j; i++)
            {
                //clonedParameters[i] = (SqlParameter)((ICloneable)originalParameters[i]).Clone();
                //SqlParameter orig = originalParameters[i];
                clonedParameters[i] = originalParameters[i];
                
                //new SqlParameter(orig.ParameterName, orig.SqlDbType, orig.Size);
            }

            return clonedParameters;
        }

        public override int ExecuteNonQuery(string connectNewString, string spName, bool nonReturnValue, params object[] parameterValues)
        {
            Connection cn1 = new Connection();
            SqlConnection cn = cn1.SqlConnect;
            cn.ConnectionString = connectNewString;
            int retval = 0;
            try
            {
                cn.Open();
                if ((parameterValues != null) && (parameterValues.Length > 0))
                {
                    //pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
                    commandParameters = GetSpParameterSet(cn, spName, nonReturnValue);

                    //assign the provided values to these parameters based on parameter order
                    AssignParameterValues(commandParameters, parameterValues);
                }

                SqlCommand cmd = new SqlCommand();
                PrepareCommand(cmd, cn, (SqlTransaction)null, CommandType.StoredProcedure, spName, commandParameters);

                //finally, execute the command.
                retval = cmd.ExecuteNonQuery();

            }
            catch (Exception ex)
            {
                //ExceptionLogManager.LogException(ex, "Generated from file ETUDataControls -> ETUSQLDataLayer.cs; Method ExecuteNonQuery", "ETUDataControls", null);
                throw new Exception(ex.Message, ex.InnerException);
            }
            finally
            {
                if (cn.State == ConnectionState.Open)
                    cn.Close();
            }
            return retval;
        }

        public override int ExecuteNonQuery(string spName, bool nonReturnValue, params object[] parameterValues)
        {
            SqlConnection cn;
            if (string.IsNullOrEmpty(connectString))
            {
                Connection cn1 = new Connection();
                cn = cn1.SqlConnect;
                cn.ConnectionString = cn1.ConnectString;
            }
            else
            {

                cn = new SqlConnection(connectString);
            }
            int retval = 0;
            try
            {
                cn.Open();
                if ((parameterValues != null) && (parameterValues.Length > 0))
                {
                    //pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
                    commandParameters = GetSpParameterSet(cn, spName, nonReturnValue);

                    //assign the provided values to these parameters based on parameter order
                    AssignParameterValues(commandParameters, parameterValues);
                }

                SqlCommand cmd = new SqlCommand();
                PrepareCommand(cmd, cn, (SqlTransaction)null, CommandType.StoredProcedure, spName, commandParameters);

                //finally, execute the command.
                retval = cmd.ExecuteNonQuery();

            }
            catch (Exception ex)
            {
                //ExceptionLogManager.LogException(ex, "Generated from file ETUDataControls -> ETUSQLDataLayer.cs; Method ExecuteNonQuery", "ETUDataControls", null);
                throw new Exception(ex.Message, ex.InnerException);
            }
            finally
            {
                if (cn.State == ConnectionState.Open)
                    cn.Close();
            }
            return retval;
        }
        //new method
        public override int ExecuteNonQuery(string connectString, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
        {
            SqlConnection cn;
            if (string.IsNullOrEmpty(connectString))
            {
                Connection cn1 = new Connection();
                cn = cn1.SqlConnect;
                cn.ConnectionString = cn1.ConnectString;
            }
            else
            {
                cn = new SqlConnection(connectString);
            }
            int retval = 0;
            try
            {
                cn.Open();

                //create a command and prepare it for execution
                SqlCommand cmd = new SqlCommand();
                PrepareCommand(cmd, cn, (SqlTransaction)null, commandType, commandText, commandParameters);

                //finally, execute the command.
                retval = cmd.ExecuteNonQuery();

            }
            catch (Exception ex)
            {
                //ExceptionLogManager.LogException(ex, "Generated from file ETUDataControls -> ETUSQLDataLayer.cs; Method ExecuteNonQuery", "ETUDataControls", null);
                throw new Exception(ex.Message, ex.InnerException);
            }
            finally
            {
                if (cn.State == ConnectionState.Open)
                    cn.Close();
            }
            return retval;

        }
        public override object[] ExecuteSQLNonQueryWithOutput(string spName, params object[] parameterValues)
        {
            SqlConnection cn;
            if (string.IsNullOrEmpty(connectString))
            {
                Connection cn1 = new Connection();
                cn = cn1.SqlConnect;
                cn.ConnectionString = cn1.ConnectString;
            }
            else
            {

                cn = new SqlConnection(connectString);
            }
            try
            {
                cn.Open();
                if ((parameterValues != null) && (parameterValues.Length > 0))
                {
                    //pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
                    commandParameters = GetSpParameterSet(cn, spName, false);

                    //assign the provided values to these parameters based on parameter order
                    AssignParameterValues(commandParameters, parameterValues);
                }

                SqlCommand cmd = new SqlCommand();
                PrepareCommand(cmd, cn, (SqlTransaction)null, CommandType.StoredProcedure, spName, commandParameters);

                int ii = 0;
                foreach (SqlParameter op in commandParameters)
                {

                    if (op.Direction == ParameterDirection.InputOutput || op.Direction == ParameterDirection.Output)
                    {
                        ii++;
                    }
                }

                object[] retval = new object[ii];
                //execute command
                cmd.ExecuteNonQuery();

                ii = 0;
                foreach (SqlParameter op in commandParameters)
                {
                    if (op.Direction == ParameterDirection.InputOutput || op.Direction == ParameterDirection.Output)
                    {
                        retval.SetValue(op.Value, ii);
                        ii++;
                    }

                }
                return retval;

            }
            catch (Exception ex)
            {
                //ExceptionLogManager.LogException(ex, "Generated from file ETUDataControls -> ETUSQLDataLayer.cs; Method ExecuteNonQuery", "ETUDataControls", null);
                throw new Exception(ex.Message, ex.InnerException);
            }
            finally
            {
                if (cn.State == ConnectionState.Open)
                    cn.Close();
            }
            
            
        }

        public override object[] ExecuteSQLNonQueryWithOutput(string connectNewString, string spName, params object[] parameterValues)
        {
            Connection cn1 = new Connection();
            SqlConnection cn = cn1.SqlConnect;
            cn.ConnectionString = connectNewString;
            try
            {
                cn.Open();
                if ((parameterValues != null) && (parameterValues.Length > 0))
                {
                    //pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
                    commandParameters = GetSpParameterSet(cn, spName, false);

                    //assign the provided values to these parameters based on parameter order
                    AssignParameterValues(commandParameters, parameterValues);
                }

                SqlCommand cmd = new SqlCommand();
                PrepareCommand(cmd, cn, (SqlTransaction)null, CommandType.StoredProcedure, spName, commandParameters);
                int ii = 0;
                
                foreach (SqlParameter op in commandParameters)
                {
                    
                    if (op.Direction == ParameterDirection.InputOutput || op.Direction == ParameterDirection.Output)
                    {
                        ii++;
                    }
                }

                object[] retval = new object[ii];
                //execute command
                cmd.ExecuteNonQuery();

                ii = 0;
                foreach (SqlParameter op in commandParameters)
                {
                    if (op.Direction == ParameterDirection.InputOutput || op.Direction == ParameterDirection.Output)
                    {
                        retval.SetValue(op.Value, ii);
                        ii++;
                    }

                }
                return retval;

            }
            catch (Exception ex)
            {
                //ExceptionLogManager.LogException(ex, "Generated from file ETUDataControls -> ETUSQLDataLayer.cs; Method ExecuteNonQuery", "ETUDataControls", null);
                throw new Exception(ex.Message, ex.InnerException);
            }
            finally
            {
                if (cn.State == ConnectionState.Open)
                    cn.Close();
            }
            
            
        }
        public override DataSet ExecuteDataSet(string connectNewString, string spName, params object[] parameterValues)
        {
            Connection cn1 = new Connection();
            SqlConnection cn = cn1.SqlConnect;
            cn.ConnectionString = connectNewString;
            DataSet ds = new DataSet();
            try
            {
                cn.Open();

                if ((parameterValues != null) && (parameterValues.Length > 0))
                {
                    //pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
                    commandParameters = GetSpParameterSet(cn, spName, false);

                    //assign the provided values to these parameters based on parameter order
                    AssignParameterValues(commandParameters, parameterValues);
                }

                SqlCommand cmd = new SqlCommand();
                PrepareCommand(cmd, cn, (SqlTransaction)null, CommandType.StoredProcedure, spName, commandParameters);

                SqlDataAdapter da = new SqlDataAdapter(cmd);

                da.Fill(ds);

            }
            catch (Exception ex)
            {
                //ExceptionLogManager.LogException(ex, "Generated from file ETUDataControls -> ETUSQLDataLayer.cs; Method ExecuteDataSet", "ETUDataControls", null);
                throw new Exception(ex.Message, ex.InnerException);
            }
            finally
            {
                if (cn.State == ConnectionState.Open)
                    cn.Close();
            }
            return ds;
        }
        //new method
        public override DataSet ExecuteDataset(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
        {
            SqlConnection cn;
            if (string.IsNullOrEmpty(connectString))
            {
                Connection cn1 = new Connection();
                cn = cn1.SqlConnect;
                cn.ConnectionString = cn1.ConnectString;
            }
            else
            {

                cn = new SqlConnection(connectString);
            }
            DataSet ds = new DataSet();
            try
            {
                cn.Open();

                SqlCommand cmd = new SqlCommand();
                PrepareCommand(cmd, cn, (SqlTransaction)null, commandType, commandText, commandParameters);

                //create the DataAdapter & DataSet
                SqlDataAdapter da = new SqlDataAdapter(cmd);

                //fill the DataSet using default values for DataTable names, etc.
                da.Fill(ds);

                // detach the SqlParameters from the command object, so they can be used again.			
                cmd.Parameters.Clear();


            }
            catch (Exception ex)
            {
                //ExceptionLogManager.LogException(ex, "Generated from file ETUDataControls -> ETUSQLDataLayer.cs; Method ExecuteDataSet", "ETUDataControls", null);
                throw new Exception(ex.Message, ex.InnerException);
            }
            finally
            {
                if (cn.State == ConnectionState.Open)
                    cn.Close();
            }
            return ds;
        }
        public override DataSet ExecuteDataSet(string spName, params object[] parameterValues)
        {
            SqlConnection cn;
            if (string.IsNullOrEmpty(connectString))
            {
                Connection cn1 = new Connection();
                cn = cn1.SqlConnect;
                cn.ConnectionString = cn1.ConnectString;
            }
            else
            {

                cn = new SqlConnection(connectString);
            }
            DataSet ds = new DataSet();
            try
            {
                cn.Open();

                if ((parameterValues != null) && (parameterValues.Length > 0))
                {
                    //pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
                    commandParameters = GetSpParameterSet(cn, spName, false);

                    //assign the provided values to these parameters based on parameter order
                    AssignParameterValues(commandParameters, parameterValues);
                }

                SqlCommand cmd = new SqlCommand();
                PrepareCommand(cmd, cn, (SqlTransaction)null, CommandType.StoredProcedure, spName, commandParameters);

                SqlDataAdapter da = new SqlDataAdapter(cmd);

                da.Fill(ds);

            }
            catch (Exception ex)
            {
                //ExceptionLogManager.LogException(ex, "Generated from file ETUDataControls -> ETUSQLDataLayer.cs; Method ExecuteDataSet", "ETUDataControls", null);
                throw new Exception(ex.Message, ex.InnerException);
            }
            finally
            {
                if (cn.State == ConnectionState.Open)
                    cn.Close();
            }
            return ds;
        }

        
        private void PrepareCommand(SqlCommand command, SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, SqlParameter[] commandParameters)
        {
            //associate the connection with the command
            command.Connection = connection;

            //set the command text (stored procedure name or SQL statement)
            command.CommandText = commandText;

            //if we were provided a transaction, assign it.
            if (transaction != null)
            {
                command.Transaction = transaction;
            }

            //set the command type
            command.CommandType = commandType;

            command.CommandTimeout = 40000;


            //attach the command parameters if they are provided
            if (commandParameters != null)
            {
                AttachParameters(command, commandParameters);
            }

            return;
        }
        private static void AssignParameterValues(SqlParameter[] commandParameters, object[] parameterValues)
        {
            if ((commandParameters == null) || (parameterValues == null))
            {
                //do nothing if we get no data
                return;
            }

            // we must have the same number of values as we pave parameters to put them in
            if (commandParameters.Length != parameterValues.Length)
            {
                throw new ArgumentException("Parameter count does not match Parameter Value count.");
            }

            //iterate through the SqlParameters, assigning the values from the corresponding position in the 
            //value array
            for (int i = 0, j = commandParameters.Length; i < j; i++)
            {
                commandParameters[i].Value = parameterValues[i];
            }
        }
        private static void AttachParameters(SqlCommand command, SqlParameter[] commandParameters)
        {
            foreach (SqlParameter p in commandParameters)
            {
                //check for derived output value with no value assigned
                
                if ((p.Direction == ParameterDirection.InputOutput) && (p.Value == null))
                {
                    p.Value = DBNull.Value;
                }

                command.Parameters.Add(p);
            }
        }
        public string ConnectString
        {
            get { return connectString; }
            set { connectString = value; }
        }

    }
}

